/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAL;

import Entity.Car;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author MrKoi
 */
public class CarBean {
    Collection ListCar;
    Collection ListCarID,CarBookID,listcarsearch,listnew;
    CallableStatement cl=null;
    Connect c=new Connect();
    Connection con;
    public CarBean() {
    }
    private void closeCnn(){
        try {
            cl.close();
            con.close();
        } catch (SQLException ex) {
            Logger.getLogger(AdminBean.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    //check status car
    public int getquantity(String id)
    {
        con=c.getConnection();
        int num=0;
        try
        {
            cl=con.prepareCall("{call GetQuantityCar_sp(?)}");
            cl.setInt("CarId",Integer.valueOf(id));
            ResultSet rs=cl.executeQuery();
            while(rs.next())
            {
                num=rs.getInt("Quantity");
            }
            closeCnn();
         }catch (Exception ex){closeCnn(); return num;}
        
        return num;
    }
    //update status
    public boolean UpdateQuantity(int quantity,String carid)
    {
        con=c.getConnection();
        try
        {
            cl=con.prepareCall("{call UpdateQuantityCar_sp(?,?)}");
            cl.setInt("Quantity", quantity);
            cl.setString("CarId", carid);
            int rs=cl.executeUpdate();
            if(rs > 0)
            {
                return true;
            }
            closeCnn();
        }catch (Exception ex){closeCnn();return false;}
        return false;
    }
    //hi?n th? top 10 
    public Collection SelectTop10()
    {
        con=c.getConnection();
         listnew=new ArrayList();
        try
        {
            cl=con.prepareCall("{call SelectTop10Car_sp}");
            ResultSet rs=cl.executeQuery();
            while(rs.next())
            {
                Car car=new Car();
                car.setCarID(rs.getString("CarID"));
                car.setImage(rs.getString("Image"));
                car.setCarName(rs.getString("CarName"));
                listnew.add(car);
            }
            closeCnn();
        }catch (Exception ex){closeCnn();}
        return listnew;
    }
    //search car
    public Collection SearchCar(String seatfrom,String seatto,String air,String type, String modelid)
    {
        con=c.getConnection();
        listcarsearch=new ArrayList();
        try
        {
            int seatf=Integer.parseInt(seatfrom);
            int seatt=Integer.parseInt(seatto);
            int mid=Integer.parseInt(modelid);
            cl=con.prepareCall("{call SearchCar_sp(?,?,?,?,?)}");
            cl.setString("Air", air);
            cl.setString("CarType",type);
            cl.setInt("Seatfrom ", seatf);
            cl.setInt("Seatto", seatt);
            cl.setInt("ModelId", mid);
            ResultSet rs=cl.executeQuery();
            while(rs.next())
            {
                Car car=new Car();
                car.setCarID(rs.getString("CarID"));
                car.setImage(rs.getString("Image"));
                car.setCarName(rs.getString("CarName"));
                car.setSeating(rs.getInt("Seating"));
                car.setAirConditioner(rs.getString("Air_conditioner"));
                car.setPriceDate(rs.getFloat("PriceDate"));
                car.setPriceHour(rs.getFloat("PriceHour"));
                car.setPriceKilomet(rs.getFloat("Pricekilomet"));
                car.setCarType(rs.getString("CarType"));
                car.setModelName(rs.getString("ModelName"));
                listcarsearch.add((Object)car);
            }
            closeCnn();
        }catch (Exception ex){}
        return listcarsearch;
    }
    //select theo carid display booking
    public Collection SeclectCarBookID(String id)
    {
        con=c.getConnection();
        CarBookID=new ArrayList();
        try
        {
            cl=con.prepareCall("{call SeclectCarBookID_sp(?)}");
            cl.setString(1,id);
            ResultSet rs=cl.executeQuery();
            while(rs.next())
            {
                Car car=new Car();
                car.setCarID(rs.getString("CarID"));
                car.setImage(rs.getString("Image"));
                car.setCarName(rs.getString("CarName"));
                car.setModelName(rs.getString("ModelName"));
                CarBookID.add(car);
            }
            closeCnn();
        }catch (Exception ex){}
        return CarBookID;
    }
    //select all
    public Collection SelectCar()
    {
        con=c.getConnection();
        ListCar=new ArrayList();
        try
        {
             cl=con.prepareCall("{call SelectCar_sp}");
            ResultSet rs=cl.executeQuery();
            while(rs.next())
            {
                Car car=new Car();
                car.setCarID(rs.getString("CarID"));
                car.setImage(rs.getString("Image"));
                car.setCarName(rs.getString("CarName"));
                car.setSeating(rs.getInt("Seating"));
                car.setAirConditioner(rs.getString("Air_conditioner"));
                car.setQuantity(rs.getInt("Quantity"));
                car.setPriceDate(rs.getFloat("PriceDate"));
                car.setPriceHour(rs.getFloat("PriceHour"));
                car.setPriceKilomet(rs.getFloat("Pricekilomet"));
                car.setCarType(rs.getString("CarType"));
                car.setModelName(rs.getString("ModelName"));
                car.setDescription(rs.getString("Description"));
                car.setState(rs.getString("State"));
                ListCar.add(car);
            }
            closeCnn();
        }catch (Exception ex){closeCnn();}
        return ListCar;
    }
    //select theo car id hien thi chi tiet
    public Collection SelectCarID(String id)
    {
        con=c.getConnection();
        ListCarID=new ArrayList();
        try
        {
            cl=con.prepareCall("{call SelectCarID_sp(?)}");
            cl.setString(1,id);
            ResultSet rs=cl.executeQuery();
            while(rs.next())
            {
                Car car=new Car();
                car.setCarID(rs.getString("CarID"));
                car.setImage(rs.getString("Image"));
                car.setCarName(rs.getString("CarName"));
                car.setSeating(rs.getInt("Seating"));
                car.setAirConditioner(rs.getString("Air_conditioner"));
                car.setPriceDate(rs.getFloat("PriceDate"));
                car.setPriceHour(rs.getFloat("PriceHour"));
                car.setPriceKilomet(rs.getFloat("Pricekilomet"));
                car.setCarType(rs.getString("CarType"));
                car.setModelName(rs.getString("ModelName"));
                car.setDescription(rs.getString("Description"));
                car.setState(rs.getString("State"));
                ListCarID.add(car);
            }
            closeCnn();
        }catch (Exception ex){closeCnn();}
        return ListCarID;
    }
    //insert car
    public boolean InsertCar(String carid,String image,String carname,int seating,String air,String Quanlity,float pricedate, float pricehour,float pricekilomet,String cartype,int modelid,String description)
    {
        con=c.getConnection();
        try
        {
            cl=con.prepareCall("{call InsertCar_sp(?,?,?,?,?,?,?,?,?,?,?,?,?)}");
            cl.setString("CarID",carid);
            cl.setString("Image",image);
            cl.setString("CarName",carname);
            cl.setInt("Seating",seating);
            cl.setString("Air_conditioner",air);
            cl.setInt("Quantity",Integer.parseInt(Quanlity));
            cl.setFloat("PriceDate",pricedate);
            cl.setFloat("PriceHour ",pricehour);
            cl.setFloat("Pricekilomet",pricekilomet);
            cl.setString("CarType",cartype);
            cl.setInt("ModelID",modelid);
            cl.setString("Description",description);
            cl.setString("State","True");
            int rs=cl.executeUpdate();
            if(rs >0)
            {
                return true;
            }
            closeCnn();
        }catch (Exception ex){closeCnn();return false;}
        return false;
    }
    //update car have image
     public boolean UpdateCarInmgae(String carid,String image,String carname,int seating,String air,String Quanlity,float pricedate, float pricehour,float pricekilomet,String cartype,int modelid,String description)
    {
        con=c.getConnection();
        try
        {
            cl=con.prepareCall("{call UpdateCarInmgae_sp(?,?,?,?,?,?,?,?,?,?,?,?)}") ;           
            cl.setString("CarID",carid);
            cl.setString("Image",image);
            cl.setString("CarName",carname);
            cl.setInt("Seating",seating);
            cl.setString("Air_conditioner",air);
            cl.setInt("Quantity",Integer.parseInt(Quanlity));
            cl.setFloat("PriceDate",pricedate);
            cl.setFloat("PriceHour ",pricehour);
            cl.setFloat("Pricekilomet",pricekilomet);
            cl.setString("CarType",cartype);
            cl.setInt("ModelID",modelid);
            cl.setString("Description",description);
            cl.setString("State","True");
            int rs=cl.executeUpdate();
            if(rs >0)
            {
                return true;
            }
            closeCnn();
        }catch (Exception ex){closeCnn();return false;}
        return false;
    }
     //update car haven't image
      public boolean UpdateCarNoInmgae(String carid,String carname,int seating,String air,String quanlity,float pricedate, float pricehour,float pricekilomet,String cartype,int modelid,String description)
    {
        con=c.getConnection();
        try
        {
            cl=con.prepareCall("{call UpdateCarNoInmgae(?,?,?,?,?,?,?,?,?,?,?)}");            
            cl.setString("CarID",carid);
            
            cl.setString("CarName",carname);
            cl.setInt("Seating",seating);
            cl.setString("Air_conditioner",air);
            cl.setInt("Quantity",Integer.parseInt(quanlity));
            cl.setFloat("PriceDate",pricedate);
            cl.setFloat("PriceHour ",pricehour);
            cl.setFloat("Pricekilomet",pricekilomet);
            cl.setString("CarType",cartype);
            cl.setInt("ModelID",modelid);
            cl.setString("Description",description);
            cl.setString("State","True");
            int rs=cl.executeUpdate();
            if(rs >0)
            {
                return true;
            }
            closeCnn();
        }catch (Exception ex){return false;}
        return false;
    }
      //delete car
     public boolean DeleteCar(String id)
     {
        con=c.getConnection();
        try
        {
            CallableStatement cl=null;
            cl=con.prepareCall("{call DeleteCar_sp(?)}");
            cl.setString("CarID",id);
           int rs= cl.executeUpdate();
           if(rs>0)
           {
               cl.close();
                con.close();
               return true;
           }else{
               cl.close();
                con.close();
               return  false;
           }
        }catch (Exception ex){return false;}
     }
     public Float seletePriceDay(String carid)
     {
         con=c.getConnection();
         float priceday = 0;
        try
        {
            cl=con.prepareCall("{call seletePriceDay_sp(?)}");
            cl.setString(1,carid);
            ResultSet rs=cl.executeQuery();
            while(rs.next())
            {
              priceday=rs.getFloat("PriceDate");
            }
            closeCnn();
        }catch (Exception ex){ closeCnn(); }
         return priceday;
     }
      public Float seletePriceHour(String carid)
     {
         con=c.getConnection();
         float pricehour = 0;
        try
        {
            cl=con.prepareCall("{call seletePriceHour_sp(?)}");
            cl.setString(1,carid);
            ResultSet rs=cl.executeQuery();
            while(rs.next())
            {
              pricehour=rs.getFloat("PriceHour");
            }
            closeCnn();
        }catch (Exception ex){ closeCnn(); }
         return pricehour;
     }
       public Float seletePricekilomet(String carid)
     {
         con=c.getConnection();
         float pricekilomet = 0;
        try
        {
            cl=con.prepareCall("{call seletePricekilomet_sp(?)}");
            cl.setString(1,carid);
            ResultSet rs=cl.executeQuery();
            while(rs.next())
            {
              pricekilomet=rs.getFloat("Pricekilomet");
            }
            closeCnn();
        }catch (Exception ex){ closeCnn(); }
         return pricekilomet;
     }
       public static void main(String[] aaa){
           CarBean cb= new CarBean();
              System.out.println(cb.DeleteCar("c0015"));              
       }
}
